﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;

namespace CrmApplication.Models
{
    public class CountyModels
	{
		#region RegionMappingCounty

		public decimal Id{ get; set; }
        public string Name { get; set; }

		#endregion

		#region RegionOracleConnect

		private static OracleConnection connect;

        static CountyModels()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }
		#endregion

		#region RegionCounty
		/// <summary>
        /// select all counties for combobox
        /// </summary>
        /// <returns></returns>
        public static List<CountyModels> GetCounty(string searchCounty)
        {
            string selectCounty = "where county_name like '" + searchCounty + "%'";
            string selectString = "SELECT id_county,county_name from nomcounty "+
                                        selectCounty+
                                        " order by county_name ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            List<CountyModels> result = new List<CountyModels>();
            CountyModels result1 = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new CountyModels();
                        result1.Id = sdr.GetDecimal(0);
                        result1.Name = sdr.GetString(1);                        
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// add new county
        /// </summary>
        /// <param name="countyName"></param>
        public static void AddCounty(string countyName)
        {
            string insert = "INSERT INTO nomcounty(county_name) VALUES (:countyName)";

            OracleCommand usercomm = new OracleCommand(insert);

            usercomm.Parameters.Add(":countyName",countyName);

            usercomm.Connection = connect;
            try
            {
                connect.Open();
                usercomm.ExecuteNonQuery();
            }
            finally
            {
                connect.Close();
            }
        }

		/// <summary>
		/// check if countu name exists
		/// </summary>
		/// <param name="county"></param>
		/// <returns></returns>
        public static CountyModels CheckCounty(string county)
        {
            string selectString = "select id_county,county_name from nomcounty where county_name=:countyName ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":countyName", county);

            CountyModels result = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new CountyModels();
                        result.Id = sdr.GetDecimal(0);
                        result.Name = sdr.GetString(1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// creat county array for json
        /// </summary>
        /// <param name="searchCounty"></param>
        /// <returns></returns>
        public static CountyContainer GetCountyArray(string searchCounty)
        {
            CountyModels[] data = new CountyModels[20];
            data = CountyModels.GetCounty(searchCounty).ToArray();

            CountyContainer container = new CountyContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
		}
		#endregion
	}

    public class CountyContainer
    {
        public CountyModels[] Records { get; set; }
        public int Count { get; set; }
    }
}